﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.Gateway;
using HRM.DAL.Report;

namespace HRM.ReportGateway
{
    public class RptSalaryGateway:DBGateway 
    {
        public List<SalaryReport> SalaryReportByProject(int projectId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.Name,emp.JoiningDate,emp.Status,con.FirstContact,tblProjects.ProjectName,tblSalary.Gross
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID full OUTER JOIN 
                tblSalary on emp.EmployeeId=tblSalary.EmpId
                where (tblProjects.ProjectID = '" + projectId + "' and emp.Status='Active')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<SalaryReport> salaryReport = new List<SalaryReport>();
                while (reader.Read())
                {
                    SalaryReport report = new SalaryReport();
                    report.Code = reader[0].ToString();
                    report.Designation = reader[1].ToString();
                    report.Name = reader[2].ToString();
                    DateTime dt = Convert.ToDateTime(reader[3]);
                    report.DOJ = dt.ToShortDateString();
                    report.Status = reader[4].ToString();
                    report.Contact = reader[5].ToString();
                    report.Project = reader[6].ToString();
                    if (reader[7].ToString() != "")
                    {
                        report.Gross = Convert.ToDecimal(reader[7]);
                    }
                    salaryReport.Add(report);
                }
                return salaryReport;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Creating Employee Salary Report");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<SalaryReport> AllEmployeeSalaryReport()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.Name,emp.JoiningDate,emp.Status,con.FirstContact,tblProjects.ProjectName,tblSalary.Gross
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID full OUTER JOIN 
                tblSalary on emp.EmployeeId=tblSalary.EmpId
                where (emp.Status='Active')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<SalaryReport> salaryReport = new List<SalaryReport>();
                while (reader.Read())
                {
                    SalaryReport report = new SalaryReport();
                    report.Code = reader[0].ToString();
                    report.Designation = reader[1].ToString();
                    report.Name = reader[2].ToString();
                    DateTime dt = Convert.ToDateTime(reader[3]);
                    report.DOJ = dt.ToShortDateString();
                    report.Status = reader[4].ToString();
                    report.Contact = reader[5].ToString();
                    report.Project = reader[6].ToString();
                    if (reader[7].ToString() != "")
                    {
                        report.Gross = Convert.ToDecimal(reader[7]);
                    }
                    salaryReport.Add(report);
                }
                return salaryReport;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Creating Employee Salary Report");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<SalaryReport> SalaryReportByCatagory(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT  distinct(tblCode.CodeName),desig.Title,emp.Name,emp.JoiningDate,emp.Status,con.FirstContact,tblProjects.ProjectName,tblSalary.Gross
                FROM tblCode FULL OUTER JOIN tblEmployees as emp ON tblCode.Id = emp.Code_CodeID FULL OUTER JOIN tblDesignations as desig on emp.DesignationID=desig.Id FULL OUTER JOIN
                tblPhotos ON emp.EmployeeId = tblPhotos.EmployeeID FULL OUTER JOIN tblContacts as con ON emp.EmployeeId = con.EmployeeID FULL OUTER JOIN tblProjects ON emp.ProjectID = tblProjects.ProjectID FULL OUTER JOIN
                tblCatagories ON emp.CatagoryID = tblCatagories.CatagoryID FULL OUTER JOIN tblCertificate ON emp.EmployeeId = tblCertificate.EmpID full OUTER JOIN 
                tblSalary on emp.EmployeeId=tblSalary.EmpId
                where (tblCatagories.CatagoryID = '"+id+"' and emp.Status='Active')";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<SalaryReport> salaryReport = new List<SalaryReport>();
                while (reader.Read())
                {
                    SalaryReport report = new SalaryReport();
                    report.Code = reader[0].ToString();
                    report.Designation = reader[1].ToString();
                    report.Name = reader[2].ToString();
                    DateTime dt = Convert.ToDateTime(reader[3]);
                    report.DOJ = dt.ToShortDateString();
                    report.Status = reader[4].ToString();
                    report.Contact = reader[5].ToString();
                    report.Project = reader[6].ToString();
                    if (reader[7].ToString() != "")
                    {
                        report.Gross = Convert.ToDecimal(reader[7]);
                    }
                    salaryReport.Add(report);
                }
                return salaryReport;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Creating Employee Salary Report");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}